package com.boss.salary.dao.impl;

import com.alibaba.fastjson.JSONArray;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IAgencyInfoDao;
import com.boss.salary.entity.AgencyInfo;
import com.boss.salary.entity.AgencyVerify;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * @author :李帅
 * date：2020/10/16
 */
@Repository
public class AgencyInfoDaoImpl extends BaseDaoImpl implements IAgencyInfoDao {

    @Override
    public void insertAgencyInfo(AgencyInfo agencyInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_INFO);
        sql.append("(id,biz_key,create_time,update_time,agency_name,")
                .append("mof_div_code,location,parent_id,level_no,is_leaf,")
                .append("agency_code,agency_abbreviation,unifsoc_cred_code,agency_type,agency_state,")
                .append("is_deleted)")
                .append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        Object[] params = new Object[]{
                agencyInfo.getId(),agencyInfo.getBizKey(),agencyInfo.getCreateTime(),agencyInfo.getUpdateTime(),
                agencyInfo.getAgencyName(),agencyInfo.getMofDivCode(),agencyInfo.getLocation(),agencyInfo.getParentId(),
                agencyInfo.getLevelNo(),agencyInfo.getIsLeaf(),agencyInfo.getAgencyCode(),agencyInfo.getAgencyAbbreviation(),
                agencyInfo.getUnifsocCredCode(),agencyInfo.getAgencyType(),agencyInfo.getAgencyState(),
                agencyInfo.getIsDeleted()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteAgencyInfo(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_INFO);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateAgencyInfo(AgencyInfo agencyInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_INFO);
        sql.append(" set biz_key=?, create_time=?, update_time=?, agency_name=?, mof_div_code=?,")
                .append("location=?, parent_id=?, level_no=?, is_leaf=?, agency_code=?, agency_abbreviation=?,")
                .append("unifsoc_cred_code=?, agency_type=?, agency_state=?, is_deleted=?,is_payment_agency=?,is_submit_able=?,remarks=? where id=?");
        Object[] params = new Object[]{
                agencyInfo.getBizKey(),agencyInfo.getCreateTime(),agencyInfo.getUpdateTime(),
                agencyInfo.getAgencyName(),agencyInfo.getMofDivCode(),agencyInfo.getLocation(),agencyInfo.getParentId(),
                agencyInfo.getLevelNo(),agencyInfo.getIsLeaf(),agencyInfo.getAgencyCode(),agencyInfo.getAgencyAbbreviation(),
                agencyInfo.getUnifsocCredCode(),agencyInfo.getAgencyType(),agencyInfo.getAgencyState(),
                agencyInfo.getIsDeleted(),agencyInfo.getIsPaymentAgency(),agencyInfo.getIsSubmitAble(),agencyInfo.getRemarks(),agencyInfo.getId(),
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public int queryAgencyInfoTotal(Map<String, Object> queryParam, List<String> childrenId) {
        List<String> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.AGENCY_INFO).append(" a")
                .append(" join ").append(TableName.AGENCY_EXT).append(" b")
                .append(" on a.id = b.agency_id")
                .append(" where a.is_deleted = 0 and b.is_deleted = 0 ");
        sql.append(" and ").append(SysUtils.inSql("a.id", childrenId));
        if (!StringUtils.isEmpty(queryParam)) {
            String flag = queryParam.get("flag").toString();
            if (!"all".equals(flag)) {
                sql.append(" and b.verify_state = ? ");
                paramList.add(flag);
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public List<AgencyInfo> queryAgencyInfoPage(PaginationDTO paginationDTO, Map<String, Object> queryParam, List<String> childrenId) {
        List<String> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.*, b.agency_id, b.agency_address, b.zip, b.agency_adm_level_code,")
                .append(" b.adm_dep_type_code, b.agency_bank_name, b.agency_bank_cardno, b.central_agency_code,")
                .append(" b.agency_leader, b.is_uni_sala,b.has_file,b.fund_sup_code,")
                .append(" b.capital_type, b.effective_year, b.verify_state, b.message ,b.update_time as updateTime")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a")
                .append(" join ").append(TableName.AGENCY_EXT).append(" b")
                .append(" on a.id = b.agency_id")
                .append(" where a.is_deleted = 0 and b.is_deleted = 0 ");
        sql.append(" and ").append(SysUtils.inSql("a.id", childrenId));
//        sql.append(" where a.id = '").append(queryParam.get("agencyId")).append("'");
        if (!StringUtils.isEmpty(queryParam)) {
            String flag = queryParam.get("flag").toString();
            if (!"all".equals(flag)) {
                sql.append(" and b.verify_state = ? ");
                paramList.add(flag);
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and a.mof_div_code=?");
                paramList.add(queryParam.get("mofDivCode").toString());
            }
        }
        sql.append(" order by a.agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(AgencyInfo.class), params);
    }

    @Override
    public Map<String, Object> selectAgencyNameAndState(String id) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" aio.agency_name agencyName, ")
                .append(" avy.is_submit_in_agency isSubmitInAgency, ")//单位内部工资是否送审
                .append(" avy.salary_verified_in_agency_code salaryVerifiedInAgencyCode, ")//机构内部工资审核状态
                .append(" avy.is_salary_submit isSalarySubmit ")//工资信息是否上报
                .append(" from ")
                .append(TableName.AGENCY_INFO).append(" aio ")
                .append(" left join ")
                .append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" aio.id = avy.agency_id ")
                .append(" and avy.is_deleted = 0 and avy.is_month_end = 0 ")
                .append(" where ").append(" aio.is_deleted = 0 and aio.id = ? ");
        try{
            return super.getjdbcTemplate().queryForMap(sb.toString(), id);
        }catch (EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @Override
    public List<AgencyInfo> queryAgencyInfo(List<String> childrenId) {
        List<String> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.*, b.agency_id, b.agency_address, b.zip, b.agency_adm_level_code,")
                .append(" b.adm_dep_type_code, b.agency_bank_name, b.agency_bank_cardno, b.central_agency_code,")
                .append(" b.agency_leader, b.is_uni_sala, b.remarks,b.has_file,b.fund_sup_code,")
                .append(" b.capital_type, b.effective_year, b.verify_state,is_payment_agency")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a")
                .append(" join ").append(TableName.AGENCY_EXT).append(" b")
                .append(" on a.id = b.agency_id").append(" where a.is_deleted = '0'");
        sql.append(" and ").append(SysUtils.inSql("a.id", childrenId));
        sql.append(" order by a.agency_code");
        //请求参数初始化
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyInfo.class), paramList.toArray());
    }

    @Override
    public List<AgencyInfo> queryAgencyInfoById(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.*, b.agency_id, b.agency_address, b.zip, b.agency_adm_level_code,")
                .append(" b.adm_dep_type_code, b.agency_bank_name, b.agency_bank_cardno, b.central_agency_code,")
                .append(" b.agency_leader, b.is_uni_sala, b.remarks,b.has_file,b.fund_sup_code,")
                .append(" b.capital_type, b.effective_year, b.verify_state")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a")
                .append(" join ").append(TableName.AGENCY_EXT).append(" b")
                .append(" on a.id = b.agency_id");
        sql.append(" where a.is_deleted = ?")
                .append(" and a.id = ?");
        sql.append(" order by a.create_time");
        Object[] objects = new Object[] {
                SystemDictConstant.IS_FALSE, queryParam.get("id")
        };
        //请求参数初始化
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyInfo.class), objects);
    }

    @Override
    public List<AgencyInfo> getAgencyTreeData(String agencyId,String mofDivCode) {
        List<Object> paramList = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append("select id, biz_key, agency_name, mof_div_code, parent_id, level_no,")
                .append("is_leaf, agency_code, agency_abbreviation, unifsoc_cred_code, agency_type, agency_state")
                .append(" from ").append(TableName.AGENCY_INFO);
        sql.append(" where is_deleted = '0'");
        if (!StringUtils.isEmpty(agencyId)) {
            sql.append(" and id != ?");
            paramList.add(agencyId);
        }
        if (!StringUtils.isEmpty(mofDivCode)) {
            sql.append(" and mof_div_code = ?");
            paramList.add(mofDivCode);
        }

        sql.append(" order by agency_code");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), paramList.toArray());
    }
    @Override
    public List<AgencyInfo> getAgencyTree(String agencyId,String mofDivCode) {
        List<Object> paramList = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append("select id, biz_key, agency_name, mof_div_code, parent_id, level_no,")
                .append("is_leaf, agency_code, agency_abbreviation, unifsoc_cred_code, agency_type, agency_state")
                .append(" from ").append(TableName.AGENCY_INFO);
        sql.append(" where is_deleted is not null ");
        if (!StringUtils.isEmpty(agencyId)) {
            sql.append(" where id != ?");
            paramList.add(agencyId);
        }
        if (!StringUtils.isEmpty(mofDivCode)) {
            sql.append(" and mof_div_code = ?");
            paramList.add(mofDivCode);
        }

        sql.append(" order by agency_code");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), paramList.toArray());
    }


    @Override
    public List<String> getAgencyChildrenIds(String parentId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select id from ")
                .append(TableName.AGENCY_INFO)
                .append(" where is_deleted = 0 and parent_id = ? ");
        List<String> result = super.getjdbcTemplate().queryForList(sb.toString(), String.class, parentId);
//        如果当前节点为末端节点，则返回其本身id
        if(CollectionUtils.isEmpty(result)) {
            result.add(parentId);
        }
        return result;
    }

    @Override
    public List<AgencyInfo> getAllAgency(UserDTO userDTO) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id, biz_key, agency_name, mof_div_code, parent_id, level_no,")
                .append("is_leaf, agency_code, agency_abbreviation, unifsoc_cred_code, agency_type, agency_state")
                .append(" from ").append(TableName.AGENCY_INFO);
        sql.append(" where is_deleted = '0' and mof_div_code=? ");
        sql.append(" order by agency_code");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class),userDTO.getProvince());
    }

    @Override
    public List<AgencyInfo> getAgencyInfoList(List<String> ids) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" ai.id, ai.biz_key, ai.create_time, ai.update_time, ai.agency_name, ai.mof_div_code,");
        sql.append(" ai.location, ai.parent_id, ai.level_no, ai.is_leaf, ai.agency_code, ai.agency_abbreviation,");
        sql.append(" ai.unifsoc_cred_code, ai.agency_type, ai.agency_state, ai.is_deleted, ae.agency_id,");
        sql.append(" ae.agency_address, ae.zip, ae.agency_adm_level_code, ae.adm_dep_type_code, ae.agency_bank_name,");
        sql.append(" ae.agency_bank_cardno, ae.central_agency_code, ae.agency_leader, ae.is_uni_sala, ae.remarks,");
        sql.append(" ae.has_file, ae.fund_sup_code, ae.capital_type, ae.effective_year, ae.verify_state ");
        sql.append("  FROM ").append(TableName.AGENCY_INFO).append(" ai")
                .append(" join ").append(TableName.AGENCY_EXT).append(" ae");
        sql.append(" ON ai.id = ae.agency_id");
        sql.append(" where ").append(com.boss.sys.utils.SysUtils.inSql("ai.id", ids));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyInfo.class));
    }

    @Override
    public void batchSaveAgencyHistory(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_HISTORY)
                .append("(id, history_key, biz_key, create_time, update_time, salary_month, agency_id, ")
                .append(" agency_address, zip, agency_adm_level_code, adm_dep_type_code, agency_bank_name, ")
                .append(" agency_bank_cardno, central_agency_code, agency_leader, is_uni_sala, remarks, ")
                .append(" has_file, fund_sup_code, capital_type, verify_state, effective_year, is_deleted,mof_div_code, ")
                .append(" agency_name, agency_code,parent_id )")
                .append(" values (")
                .append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 27; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteAgencyHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.AGENCY_HISTORY);
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{month});
    }

    @Override
    public AgencyInfo getNameById(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id, biz_key, agency_name, mof_div_code, parent_id, level_no,")
                .append("is_leaf, agency_code, agency_abbreviation, unifsoc_cred_code, agency_type, agency_state,is_payment_agency ")
                .append(" from ").append(TableName.AGENCY_INFO)
                .append(" where id=? and is_deleted=?");
        Object[] params = new Object[]{
                id,SystemDictConstant.IS_FALSE
        };


        return this.getjdbcTemplate().queryForObject(sql.toString(),new BeanPropertyRowMapper<>(AgencyInfo.class),params);
    }

    @Override
    public String getIdByName(String name) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id from ").append(TableName.AGENCY_INFO)
                .append(" where id=? and is_deleted=?");
        Object[] params = new Object[]{
                name,SystemDictConstant.IS_FALSE
        };
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class, params);
    }

    @Override
    public String getCodeByAgencyId(String agencyId) {

        StringBuilder sql = new StringBuilder();
        sql.append("select agency_code from ").append(TableName.AGENCY_INFO)
                .append(" where id=? ");
        Object[] params = new Object[]{
                agencyId
        };
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class, params);
    }

    @Override
    public AgencyInfo selectFirstAuthAgencyData(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id, agency_name, agency_code, is_leaf,parent_id ")
                .append(" from ").append(TableName.AGENCY_INFO)
                .append(" where ").append(" is_deleted = 0 and id = ? ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), agencyId);
    }

    @Override
    public List<String> selectSendSalaryAgencyId(JSONArray jsonArray,UserDTO userDTO) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select id from ").append(TableName.AGENCY_INFO)
                .append(" where ").append(" is_deleted = 0 and is_leaf = 1 and mof_div_code=? ");
        paramList.add(userDTO.getProvince());
        if(!CollectionUtils.isEmpty(jsonArray)&&jsonArray!=null){
            sb.append(" and id in ( ");
            for(Object json : jsonArray){
                paramList.add(json);
                if(jsonArray.indexOf(json) != jsonArray.size() -1 ){
                    sb.append(" ?, ");
                }else{
                    sb.append(" ?) ");
                }
            }
        }
        sb.append(" order by ").append(" agency_code ");
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray(), String.class);
    }

    @Override
    public List<String> selectAllAgencyIdByProvince(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" id ")
                .append(" from ").append(TableName.AGENCY_INFO)
                .append(" where ").append(" mof_div_code = ? and is_deleted = 0 and is_leaf=1");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class, province);
    }

    @Override
    public List<String> selectAgencyIdsByProvince(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select DISTINCT avy.agency_id from ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" where ").append(" avy.mof_div_code = ? and avy.is_deleted = 0 ")
                .append(" and avy.is_month_end = 0 and is_pay_data_created = 1 and avy.is_bank_data_created = 0 ");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class, province);
    }


    @Override
    public List<String> getAgencyIdsByBank(Map<String,Object> queryForm) {

        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();

        sb .append(" select ")
                .append(" avy.agency_id agencyId ")
                .append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ") .append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" pet.agency_id = avy.agency_id  ")
                .append(" where ")
                .append(" avy.is_deleted = 0  and avy.is_confirm_submit='1' ")
                .append(" and avy.is_thirteen = 0 and avy.is_month_end = 0  and avy.is_pay_data_created = 1 ");
        if (!StringUtils.isEmpty(queryForm.get("province"))){
            sb.append(" and avy.mof_div_code = ? ");
            conditions.add(queryForm.get("province"));
        }
        if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
            sb.append(" and pet.sala_card_bank=?");
            conditions.add(queryForm.get("salaCardBank"));
        }
        if(!StringUtils.isEmpty(queryForm.get("tabPane"))){
            if("0".equals(queryForm.get("tabPane")+"")){
                sb.append(" and avy.is_bank_data_created = 0 ");
            }else {
                sb.append(" and avy.is_bank_data_created = 2 ");
            }
        }
        sb.append(" group by avy.agency_id");
        List<String> result =super.getjdbcTemplate().queryForList(sb.toString(), conditions.toArray(), String.class);
        return result;
    }

    @Override
    public List<String> getIds() {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT id from ").append(TableName.AGENCY_INFO )
          .append(" where is_leaf='0'");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class);
    }

    @Override
    public List<String> getAgencyIds(List<String> agencyCodes) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT id  from ").append(TableName.AGENCY_INFO )
                .append(" where ");
        sb.append(SysUtils.inSql("agency_code",agencyCodes));
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class);
    }

    @Override
    public List<Map<String, Object>> getAgencyId(List<String> agencyCodes,String mofDivCode) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT id ,agency_code from ").append(TableName.AGENCY_INFO )
                .append(" where ");
        sb.append(SysUtils.inSql("agency_code",agencyCodes));
        sb.append(" and is_leaf = 1");
        sb.append(" and ").append(" mof_div_code =? ");
        Object[] params= new Object[]{
                mofDivCode
        };
        return super.getjdbcTemplate().queryForList(sb.toString(),params);
    }
    @Override
    public List<String> getAgencyCodes(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" agency_code  ")
                .append(" from ").append(TableName.AGENCY_INFO)
                .append(" where mof_div_code=? and is_deleted=0  and is_leaf = 1");
        try {
            return this.getJdbcTemplate().queryForList(sql.toString(),String.class,province);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<AgencyInfo> queryid(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  id  from ").append(TableName.AGENCY_INFO)
                .append(" where  is_deleted=0 and mof_div_code=?");
        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(AgencyInfo.class),province);
    }



    //财务审核-审核上报未审核单位树查询
    @Override
    public List<AgencyInfo> getNotAuditAuthAgencyTreeData(String agencyId,String mofDivCode,List<String> bizKeys) {
        List<Object> paramList = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id, a.biz_key, a.agency_name, a.mof_div_code, a.parent_id, a.level_no,")
                .append(" a.is_leaf, a.agency_code ")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a ")
                .append(" left join ").append(TableName.AGENCY_VERIFY).append(" b ")
                .append(" on a.id = b.agency_id ")
                .append(" and b.is_month_end='0' ")
                .append(" where ( ( ").append(SysUtils.inSql("b.biz_key ",bizKeys))
                .append(" and a.is_deleted = '0' ) and b.is_deleted = '0' or a.is_leaf = '0') ");
        if (!StringUtils.isEmpty(agencyId)) {
            sql.append(" and a.id != ?");
            paramList.add(agencyId);
        }
        if (!StringUtils.isEmpty(mofDivCode)) {
            sql.append(" and a.mof_div_code = ?");
            paramList.add(mofDivCode);
        }
        sql.append(" order by a.agency_code");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), paramList.toArray());
    }



    //人员信息维护-维护工资信息未审核单位树查询
    @Override
    public List<AgencyInfo> getAgencyTreeDatas(String agencyId,String mofDivCode,List<String> bizKeys) {
        List<Object> paramList = new ArrayList<>(1);
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id, a.biz_key, a.agency_name, a.mof_div_code, a.parent_id, a.level_no,")
                .append("a.is_leaf, a.agency_code ")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a ")
                .append(" left join ").append(TableName.AGENCY_VERIFY).append(" b ")
                .append(" on a.id = b.agency_id  ")
                .append(" and b.is_month_end = '0' ")
                .append(" where ( ( ")
                .append(" b.is_submit_in_agency = '0' or  ").append(SysUtils.inSql("b.biz_key ",bizKeys))
                .append("  ) and a.is_deleted = '0' and b.is_deleted = '0' or a.is_leaf<>'1' ) ");
        if (!StringUtils.isEmpty(agencyId)) {
            sql.append(" and a.id != ?");
            paramList.add(agencyId);
        }
        if (!StringUtils.isEmpty(mofDivCode)) {
            sql.append(" and a.mof_div_code = ?");
            paramList.add(mofDivCode);
        }
        sql.append(" order by a.agency_code ");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), paramList.toArray());
    }
    @Override
    public int agencyPerson(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(b.id)  FROM ")
           .append(TableName.AGENCY_INFO).append(" a ")
           .append(" left join ").append(TableName.PERSON_EXT).append(" b ")
           .append(" on a.id = b.agency_id  and b.is_deleted='0' ").append(" where  a.id=? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,id);
    }

    @Override
    public void updateAgencyExt(AgencyInfo agencyInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_EXT);
        sql.append(" set is_deleted=? where agency_id=?");
        Object[] params = new Object[]{
                agencyInfo.getIsDeleted(),agencyInfo.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateAgencyVerify(AgencyInfo agencyInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_VERIFY);
        sql.append(" set  is_deleted=?  where is_month_end = '0' and agency_id=?");
        Object[] params = new Object[]{
                agencyInfo.getIsDeleted(),agencyInfo.getId(),
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateAgencyInfoCodeParentId(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_INFO);
        sql.append(" set  agency_code=? ,agency_name =?,parent_id=?,level_no=? ,is_leaf=?  where id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify agencyInfo : updateList) {
            Object[] params = new Object[]{
                    agencyInfo.getAgencyCode(),agencyInfo.getAgencyName(),agencyInfo.getParentId(),
                    agencyInfo.getLevelNo(),agencyInfo.getIsLeaf(), agencyInfo.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 6; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }



    @Override
    public Integer getSubmitAble(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select is_submit_able FROM ")
                .append(TableName.AGENCY_INFO)
                .append(" where  id=? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,agencyId);
    }

    @Override
    public List<AgencyInfo> getSubmitAbleByAgencyIds(String province,List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" id,agency_code,agency_name,is_submit_able  ")
                .append(" from ").append(TableName.AGENCY_INFO)
                .append(" where mof_div_code=? and is_deleted=0  and is_leaf = 1 and is_submit_able=0 and ")
                .append(SysUtils.inSql("id",agencyIds));
        Object[] params = new Object[]{
                province
        };

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class), params);

    }

    @Override
    public List<AgencyVerify> getCodeByAgency(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT  max(salary_month) as  salary_month, agency_id,agency_code,is_submit_in_agency,agency_name from ").append(TableName.AGENCY_VERIFY )
                .append(" where mof_div_code = ?  and is_month_end=0  and  is_deleted=0 ")
                .append("  GROUP BY agency_code,agency_id,is_submit_in_agency,agency_name ");
        return super.getjdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(AgencyVerify.class),province);
    }

}
